const connection = require("../app/database")

class TaskService {
  async fetchTaskList(limit, offset, dateString) {
    const statement = `
      SELECT
        staff.id AS id,
        staff.name AS name,
        staff.gender AS gender,
        staff.age AS age,
        staff.working_age AS workingAge,
        staff.skills AS skill,
        staff.mobile_phone AS mobilePhone,
        IF(
          COUNT(att.id) = 0,
          JSON_ARRAY(),
          JSON_ARRAYAGG(
            JSON_OBJECT(
              'id', att.id, 'timeIndexs', att.task_time_indexs
            )
          )
        ) AS tasks
      FROM staff
      LEFT JOIN attendance att ON staff.id = att.staff_id AND DATE(att.task_date) = ?
      WHERE DATE(staff.createAt) <= ?
      GROUP BY staff.id, staff.name, staff.gender, staff.age, staff.working_age, staff.skills, staff.mobile_phone
      ORDER BY IF(COUNT(att.id) = 0, 0, 1) DESC
      LIMIT ? OFFSET ?;
    `
    const [result] = await connection.execute(statement, [dateString, dateString, limit, offset])
    return result
  }

  async update(task) {
    const statement = 'UPDATE `attendance` SET task_time_indexs = ? WHERE id = ?'
    const [result] = await connection.execute(statement, [task.timeIndexs, String(task.id)])
    return result
  }

  async create(task) {
    const statement = 'INSERT INTO `attendance` (staff_id, creator_id, task_time_indexs, task_date) VALUES (?, ?, ?, ?)'
    const [result] = await connection.execute(statement, [String(task.staffId), String(task.creatorId), task.timeIndexs, task.date])
    return result
  }
}

module.exports = new TaskService()